package com.qf.dao.Impl;

import com.qf.dao.UserDao;
import com.qf.domain.User;
import com.qf.utils.DataSourceUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {
    private QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
    @Override
    public User selectCountByUsername(String username) {
        String sql = " SELECT * from tb_user where username = ? ";
        try {
            return queryRunner.query(sql,new BeanHandler<>(User.class),username);
        } catch (SQLException e) {
            System.out.println("查询用户名是否存在失败!!");
            throw new RuntimeException(e);
        }
    }

    @Override
    public int insert(User user) {
        String sql = " insert into tb_user values(null ,?,md5(?),?,?,?,?,?) ";
        try {
            int i = queryRunner.update(sql, user.getUsername(),
                    user.getPassword(), user.getEmail(), user.getGender(), user.getFlag(), 1, "");
            return i;
        } catch (SQLException e) {
            System.out.println("注册用户失败!!");
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User selectByUsernameAndPassword(String username, String password) {
        String sql = " select * from tb_user where username = ? and password = md5(?) ";
        try {
            User user = queryRunner.query(sql, new BeanHandler<>(User.class), username, password);
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("登录失败!!");
        }
        return null;
    }

    @Override
    public User findByNameInRole(String username) {
        String sql =" SELECT * FROM `tb_user` WHERE username = ? AND role = 0 ";
        User user = null;
        try {
            user = queryRunner.query(sql, new BeanHandler<>(User.class), username);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("查询失败");
        }
        return user;
    }

    @Override
    public List<User> findAlluser() {
        String sql = " SELECT `id`,`email`, `username`,`gender`,`role` FROM `tb_user` WHERE flag = 1; ";
        try {
            List<User> query = queryRunner.query(sql, new BeanListHandler<>(User.class));
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void deleteById(int id) {
        String sql = " DELETE  FROM `tb_user` WHERE id=? ";
        try {
            queryRunner.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("删除失败!!");
        }
    }

    @Override
    public List<User> findUserByUsernameAndGender(String username, String gender) {
        String sql = " SELECT `id`,`email`,`username`,`gender`,`role` FROM `tb_user` WHERE gender = ? AND username LIKE ? ";
        try {
            List<User> users = queryRunner.query(sql, new BeanListHandler<>(User.class), gender, "%" + username + "%");
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("查询失败!!");
        }
        return null;
    }
}
